﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.Web;
using Framework;

namespace Enterprise
{
    public class ProjectService : BaseService
    {
        public DataRow GetById(int id)
        {
            return DbHelperOleDb.Query("select * from TbProject where CoProjectId={0}".FormatWith(id)).Tables["ds"].Rows[0];
        }
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable(int pageValue = 1, int rowsValue = 10, string sort = "CoSort", string order = "Desc", string name = "")
        {
            if (sort.IsNullOrEmpty())
            {
                sort = "CoSort";
            }
            if (order.IsNullOrEmpty())
            {
                order = "Desc";
            }
            if (pageValue < 1)
            {
                pageValue = 1;
            }
            if (rowsValue < 1)
            {
                rowsValue = 10;
            }
            var parameters = new[]
                {
                    new OleDbParameter("@CoName", OleDbType.VarChar, 255)
                };
            parameters[0].Value = name.SqlLike();
            var sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append(" where (CoName like '%'+@CoName+'%' or CoNameEn like '%'+@CoName+'%') ");
            int total = DbHelperOleDb.GetSingle("select count(*) from TbProject {0}".FormatWith(sbSqlWhere), parameters).SafeToInt();
            var sbSql = new StringBuilder();
            sbSql.AppendFormat("select top {0} * from TbProject {1} ", rowsValue, sbSqlWhere);
            if (pageValue > 1)
                sbSql.AppendFormat(
                    " and CoProjectId not in (select top {0} CoProjectId from TbProject {1} order by {2} {3},CoProjectId desc)  ",
                    rowsValue * (pageValue - 1), sbSqlWhere, sort, order);
            sbSql.AppendFormat(" order by {0} {1},CoProjectId desc ", sort, order);
            DataSet ds = DbHelperOleDb.Query(sbSql.ToString(), parameters);
            var table = new Table
            {
                Rows = ds.Tables["ds"].Rows,
                Total = total,
                DataTable = ds.Tables["ds"]
            };
            return table;
        }

        public int Add(string name = "", string nameEn = "", string content = "", string contentEn = "", string keyword = "", string keywordEn = "", string description = "", string descriptionEn = "", DateTime? date = null, int sort = 0, bool isDelete = false, HttpPostedFileBase filedata = null)
        {
            var fileName = string.Empty;
            var thumFileName = string.Empty;
            if (filedata != null && filedata.ContentLength > 0)
            {
                var uploadHelper = new UploadHelper
                {
                    PostedFile = filedata,
                    RelativePath = ProjectConfig.RelativePath,
                    IsAdaptive = ProjectConfig.IsAdaptive,
                    IsImage = true,
                    IsGenerateThumbnail = ProjectConfig.IsGenerateThumbnail,
                    FileMaxLength = ProjectConfig.ImageSize,
                    ThumbnailHeight = ProjectConfig.ThumbnailHeight,
                    ThumbnailWidth = ProjectConfig.ThumbnailWidth,
                    ThumbnailPrefix = ProjectConfig.ThumbnailPrefix
                };
                if (!uploadHelper.Save())
                {
                    throw new Exception(uploadHelper.ErrorMessage);
                }
                fileName = uploadHelper.FileName;
                thumFileName = uploadHelper.ThumbnailFileName;

            }
            var sbSql = new StringBuilder();
            sbSql.Append("insert into  TbProject(");
            sbSql.Append("CoName,CoNameEn,CoContent,CoContentEn,CoKeyword,CoKeywordEn,CoDescription,CoDescriptionEn,CoDate,CoSort,CoIsDelete,CoNormalImage,CoSmallImage,CoBigImage");
            sbSql.Append(") values (");
            sbSql.Append("@CoName,@CoNameEn,@CoContent,@CoContentEn,@CoKeyword,@CoKeywordEn,@CoDescription,@CoDescriptionEn,@CoDate,@CoSort,@CoIsDelete,@CoNormalImage,@CoSmallImage,@CoBigImage");
            sbSql.Append(")");
            var parameters = new[]
                {
                    new OleDbParameter("@CoName", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoNameEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoContent", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoContentEn", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoKeyword", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoKeywordEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoDescription", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoDescriptionEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoDate", OleDbType.Date),
                    new OleDbParameter("@CoSort", OleDbType.Integer),
                    new OleDbParameter("@CoDelete", OleDbType.Boolean),
                    new OleDbParameter("@CoNormalImage", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoSmallImage", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoBigImage", OleDbType.VarChar, 255)
                };
            parameters[0].Value = name;
            parameters[1].Value = nameEn;
            parameters[2].Value = content;
            parameters[3].Value = contentEn;
            parameters[4].Value = keyword;
            parameters[5].Value = keywordEn;
            parameters[6].Value = description;
            parameters[7].Value = descriptionEn;
            parameters[8].Value = date ?? DateTime.Now;
            parameters[9].Value = sort;
            parameters[10].Value = isDelete;
            parameters[11].Value = fileName;
            parameters[12].Value = thumFileName;
            parameters[13].Value = fileName;
            var newId = DbHelperOleDb.Add(sbSql.ToString(), parameters);
            return newId;
        }
        public int Edit(int id = 0, string name = "", string nameEn = "", string content = "", string contentEn = "", string keyword = "", string keywordEn = "", string description = "", string descriptionEn = "", DateTime? date = null, int sort = 0, bool isDelete = false, HttpPostedFileBase filedata = null)
        {
            var sbSql = new StringBuilder();
            sbSql.Append(" update TbProject set ");
            sbSql.Append(" CoName=@CoName,CoNameEn=@CoNameEn,CoContent=@CoContent,CoContentEn=@CoContentEn,CoKeyword=@CoKeyword,CoKeywordEn=@CoKeywordEn,CoDescription=@CoDescription,CoDescriptionEn=@CoDescriptionEn,CoSort=@CoSort");
            var fileName = string.Empty;
            var thumFileName = string.Empty;
            if (filedata != null && filedata.ContentLength > 0)
            {
                var uploadHelper = new UploadHelper
                {
                    PostedFile = filedata,
                    RelativePath = ProjectConfig.RelativePath,
                    IsAdaptive = ProjectConfig.IsAdaptive,
                    IsImage = true,
                    IsGenerateThumbnail = ProjectConfig.IsGenerateThumbnail,
                    FileMaxLength = ProjectConfig.ImageSize,
                    ThumbnailHeight = ProjectConfig.ThumbnailHeight,
                    ThumbnailWidth = ProjectConfig.ThumbnailWidth,
                    ThumbnailPrefix = ProjectConfig.ThumbnailPrefix
                };
                if (!uploadHelper.Save())
                {
                    throw new Exception(uploadHelper.ErrorMessage);
                }
                fileName = uploadHelper.FileName;
                thumFileName = uploadHelper.ThumbnailFileName;
                sbSql.Append(",CoNormalImage=@CoNormalImage,CoSmallImage=@CoSmallImage,CoBigImage=@CoBigImage");
            }
            sbSql.AppendFormat("  where CoProjectId={0}", id);
            var parameters = new[]
                {
                    new OleDbParameter("@CoName",OleDbType.VarChar, 255),
                    new OleDbParameter("@CoNameEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoContent", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoContentEn",OleDbType.LongVarWChar),
                    new OleDbParameter("@CoKeyword", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoKeywordEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoDescription", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoDescriptionEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoSort", OleDbType.Integer),
                    new OleDbParameter("@CoNormalImage", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoSmallImage", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoBigImage", OleDbType.VarChar, 255)

                };
            parameters[0].Value = name;
            parameters[1].Value = nameEn;
            parameters[2].Value = content;
            parameters[3].Value = contentEn;
            parameters[4].Value = keyword;
            parameters[5].Value = keywordEn;
            parameters[6].Value = description;
            parameters[7].Value = descriptionEn;
            parameters[8].Value = sort;
            parameters[9].Value = fileName;
            parameters[10].Value = thumFileName;
            parameters[11].Value = fileName;
            var count = DbHelperOleDb.Add(sbSql.ToString(), parameters);
            return count;
        }

        public int Delete(int id)
        {
            return DbHelperOleDb.ExecuteSql("delete from TbProject where CoProjectId={0}".FormatWith(id)).SafeToInt();
        }

    }
}
